# -*- coding: utf-8 -*-
#!/usr/bin/python

import os

def init_tbls():
    import sqlite3 as db
    database = 'data/PhoneCMS.db'
    if os.path.exists(database):
        os.unlink(database)

    conn = db.connect(database)
    cursor = conn.cursor()
    sqlList = [
        # Enabled foreign key
        """
        PRAGMA FOREIGN_KEYS=ON;
        """,


        # User table 
        """
        CREATE TABLE 'user' (
          'id' INTEGER PRIMARY KEY,
          'email' VARCHAR(128),
          'nickname' VARCHAR(128),
          'firstname' VARCHAR(64),
          'lastname' VARCHAR(64),
          'gender' INT,
          'avatur' BLOB,
          'passwd' VARCHAR(64),
          'lastLoginTime' TIMESTAMP
         );
        """,


        # News category table
        """
        CREATE TABLE 'category' (
          'id' INTEGER PRIMARY KEY,
          'name' VARCHAR(128)
         );
        """,
        
        # News table
        """
        CREATE TABLE 'news' (
          'id' INTEGER PRIMARY KEY,
          'name' VARCHAR(128),
          'content' TEXT,
          'category' INT,
          'createTime' TIMESTAMP,
          'thumbnail' BLOB,
          'status' INT,
          FOREIGN KEY(category) REFERENCES category(id)
         );
        """,

        # Album table
        """
        CREATE TABLE 'album' (
          'id' INTEGER PRIMARY KEY,
          'cover' BLOB,
          'createTime' TIMESTAMP,
          'desc' TEXT
         );
        """,

        # Image table
        """
        CREATE TABLE 'image' (
          'id' INTEGER PRIMARY KEY,
          'url' VARCHAR(1024),
          'createTime' TIMESTAMP,
          'album' INT,
          'desc' TEXT,
          FOREIGN KEY(album) REFERENCES album(id)
         );
        """,


        # Mandotory records
        """
        INSERT INTO category (id, name) values (
            1,
            'Default'
        );
        """,

        """
        INSERT INTO album (id, desc) values (
            1,
            '默认相册'
        );
        """,

        # Dummy records
        """
        INSERT INTO news (name,content,category,status) values (
            'test1',
            'SQLite支持哪些数据类型些',
            '1',
            '1'
        );
        """,

        """
        INSERT INTO news (name,content,category,status) values (
            'test1',
            'SQLite支持哪些数据类型些',
            '1',
            '1'
        );

        """,

        """
        INSERT INTO news (name,content,category,status) values (
            'test2',
            'SQLite支持哪些数据类型些',
            '1',
            '1'
        );

        """,

        """
        INSERT INTO news (name,content,category,status) values (
            'test3',
            'SQLite支持哪些数据类型些',
            '1',
            '1'
        );

        """,

        """
        INSERT INTO news (name,content,category,status) values (
            'test4',
            'SQLite支持哪些数据类型些',
            '1',
            '1'
        );

        """,

        """
        INSERT INTO news (name,content,category,status) values (
            'test5',
            'SQLite支持哪些数据类型些',
            '1',
            '1'
        );

        """
    ]

    for sql in sqlList:
        status = cursor.execute(sql)
    conn.commit()
    conn.close()
    

if __name__ == "__main__":
    init_tbls()
